SELECT
e_role_permission.`ROLE_ID` AS 'ROLE_CODE', t.`CODE` AS 'PERMISSION_CODE'
FROM
c1_permission AS t
JOIN c1_role_permission AS e_role_permission
ON (
e_role_permission.`PERMISSION_ID` = T.`ID`
)
WHERE EXISTS
(SELECT
1
FROM
c1_role_user AS e
WHERE e.`ROLE_ID` = e_role_permission.`ROLE_ID`
AND e.user_id = :userId)
OR EXISTS
(SELECT
1
FROM
c1_us_group_role AS E
JOIN c1_us_group_user AS e_us_group_user
ON (
e_us_group_user.`USER_GROUP_ID` = e.`USER_GROUP_ID`
)
WHERE E.`ROLE_ID` = e_role_permission.`ROLE_ID`
AND e_us_group_user.user_id = :userId)
ORDER BY e_role_permission.`ROLE_ID` , LENGTH(t.`CODE`) DESC